package cn.sqlexperiment.web.servlet;

import cn.sqlexperiment.web.classes.PrescripData;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/ShowPrescripServlet")
public class ShowPrescripServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String pageIndex = req.getParameter("pageIndex");
        String patino = req.getParameter("patino");
        String prescr_case_no = req.getParameter("caseno");
        if (pageIndex == null) {
            pageIndex = "1";
        }
        System.out.println(pageIndex+" "+patino+" "+prescr_case_no);
        List<PrescripData> productList = new ArrayList<>();
        Page page = new Page(Integer.valueOf(pageIndex));
        try {
            productList=InSql(page,patino,prescr_case_no);
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        for(PrescripData p:productList){
            System.out.println(p.getPrescrno()+" "+p.getPrescrpatino()+" "+p.getPresrcdocno()+" "+p.getPrescrmedi()+" "+p.getPrescrdate()+" "+p.getPrescrcaseno());
        }

        req.setAttribute("page", page);
        req.setAttribute("products", productList);
        req.getRequestDispatcher("showprescrip.jsp").forward(req, resp);
        resp.setHeader("refresh","0,showprescrip.jsp");
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }
    public static List<PrescripData> InSql(Page page, String patino, String prescr_case_no) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/hospital";
        String sname = "root";
        String password = "12345";
        Connection connection = DriverManager.getConnection(url, sname, password);

        int t= showAll(connection,patino,prescr_case_no);
        if(t==0){
            page.setTotalPages(1);
            List<PrescripData> productList=new ArrayList<>();
            PrescripData p = new PrescripData();
            p.setPresrcdocno(patino);
            p.setPrescrcaseno(prescr_case_no);
            productList.add(p);
            return productList;
        }
        page.setTotalRow(t);
        List<PrescripData> productList=selectData(connection,page,patino,prescr_case_no);

        connection.close();
        return productList;
    }
    private static List<PrescripData> selectData(Connection connection,Page page,String patino, String prescr_case_no) throws SQLException {
        List<PrescripData> productList = new ArrayList<>();
        String sql = "SELECT patient.pati_name, prescription.* FROM patient, prescription WHERE prescr_pati_no = ? AND pati_no = ? AND prescr_case_no = ? limit ?,?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, patino);
        statement.setString(2, patino);
        statement.setString(3, prescr_case_no);
        statement.setInt(4, page.getPageStartRow());
        statement.setInt(5, page.getPageSize());
        ResultSet resultSet = statement.executeQuery();
        String pati_name = new String();
        String prescr_medi = new String();
        String doc_no = new String();
        String prescr_date = new String();
        String doc_name = new String();
        while (resultSet.next()) {
            pati_name = resultSet.getString("pati_name");
            prescr_medi = resultSet.getString("prescr_medi");
            doc_no = resultSet.getString("presrc_doc_no");
            String ssql = "SELECT doctor.doc_name FROM doctor WHERE doc_no = ?";
            PreparedStatement sstmt = connection.prepareStatement(ssql);
            sstmt.setString(1, doc_no);
            ResultSet rrs = sstmt.executeQuery();
            while (rrs.next()) {
                doc_name = rrs.getString("doc_name");
            }
            prescr_date = resultSet.getString("prescr_date");
            PrescripData product=new PrescripData(doc_name,pati_name,patino,prescr_medi,prescr_date,prescr_case_no);
            productList.add(product);
        }
        resultSet.close();
        statement.close();
        return productList;
    }

    private static int showAll(Connection connection,String patino, String prescr_case_no) throws SQLException {
        int t=0;
        String sql = "SELECT prescription.* FROM prescription WHERE prescr_pati_no = ? AND prescr_case_no = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, patino);
        statement.setString(2, prescr_case_no);
        ResultSet resultSet=statement.executeQuery();
        while(resultSet.next()){
            t++;
        }
        statement.close();
        resultSet.close();
        return t;
    }

}
